package com.iplant.aps.serviceimpl.dao.sfc;

import com.iplant.aps.service.mesenum.APSOutSourceType;
import com.iplant.aps.service.mesenum.APSReplaceType;
import com.iplant.aps.service.mesenum.MESDBSource;
import com.iplant.aps.service.mesenum.MESException;
import com.iplant.aps.service.po.OutResult;
import com.iplant.aps.service.po.ServiceResult;
import com.iplant.aps.service.po.bms.BMSEmployee;
import com.iplant.aps.service.po.excel.MyExcelSheet;
import com.iplant.aps.service.po.sfc.SFCBatchBOMItem;
import com.iplant.aps.service.po.wms.WMSLineEdgeLibrary;
import com.iplant.aps.service.utils.StringUtils;
import com.iplant.aps.serviceimpl.dao.BaseDAO;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import java.text.MessageFormat;
import java.util.*;
import java.util.stream.Collectors;

public class SFCBatchBOMItemDAO extends BaseDAO {

    private static final Logger logger = LoggerFactory.getLogger(SFCBatchBOMItemDAO.class);

    private static SFCBatchBOMItemDAO Instance = null;

    private SFCBatchBOMItemDAO() {
        super();
    }

    public static SFCBatchBOMItemDAO getInstance() {
        if (Instance == null)
            Instance = new SFCBatchBOMItemDAO();
        return Instance;
    }

    /**
     * 添加或修改
     */
    public int Update(BMSEmployee wLoginUser, SFCBatchBOMItem wSFCBatchBOMItem, OutResult<Integer> wErrorCode) {
        int wResult = 0;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wSFCBatchBOMItem == null)
                return 0;

            String wSQL = "";
            if (wSFCBatchBOMItem.getID() <= 0) {
                wSQL = MessageFormat.format("INSERT INTO {0}.sfc_batchbomitem(ProductID,LineID,PartID,PartPointID," +
                                "MaterialID,MaterialNo,MaterialName,UnitID,FQTY,ReplaceType,OutSourceType," +
                                "EvenExchangeRate,MinFQTY,CreateID,CreateTime,EditID,EditTime,Active) VALUES" +
                                "(:ProductID,:LineID,:PartID,:PartPointID,:MaterialID,:MaterialNo,:MaterialName," +
                                ":UnitID,:FQTY,:ReplaceType,:OutSourceType,:EvenExchangeRate,:MinFQTY,:CreateID," +
                                "now(),:EditID,now(),:Active);",
                        wInstance.Result);
                wSFCBatchBOMItem.CreateID = wLoginUser.ID;
            } else {
                wSQL = MessageFormat.format("UPDATE {0}.sfc_batchbomitem SET ProductID = :ProductID,LineID = :LineID," +
                                "PartID = :PartID,PartPointID = :PartPointID,MaterialID = :MaterialID,MaterialNo = " +
                                ":MaterialNo,MaterialName = :MaterialName,UnitID = :UnitID,FQTY = :FQTY,ReplaceType =" +
                                " " +
                                ":ReplaceType,OutSourceType = :OutSourceType,EvenExchangeRate = :EvenExchangeRate," +
                                "MinFQTY = :MinFQTY,CreateID = :CreateID,CreateTime = :CreateTime,EditID = :EditID," +
                                "EditTime = now(),Active = :Active WHERE ID = :ID;",
                        wInstance.Result);
            }

            wSQL = this.DMLChange(wSQL);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("ID", wSFCBatchBOMItem.ID);
            wParamMap.put("ProductID", wSFCBatchBOMItem.ProductID);
            wParamMap.put("LineID", wSFCBatchBOMItem.LineID);
            wParamMap.put("PartID", wSFCBatchBOMItem.PartID);
            wParamMap.put("PartPointID", wSFCBatchBOMItem.PartPointID);
            wParamMap.put("MaterialID", wSFCBatchBOMItem.MaterialID);
            wParamMap.put("MaterialNo", wSFCBatchBOMItem.MaterialNo);
            wParamMap.put("MaterialName", wSFCBatchBOMItem.MaterialName);
            wParamMap.put("UnitID", wSFCBatchBOMItem.UnitID);
            wParamMap.put("FQTY", wSFCBatchBOMItem.FQTY);
            wParamMap.put("ReplaceType", wSFCBatchBOMItem.ReplaceType);
            wParamMap.put("OutSourceType", wSFCBatchBOMItem.OutSourceType);
            wParamMap.put("EvenExchangeRate", wSFCBatchBOMItem.EvenExchangeRate);
            wParamMap.put("MinFQTY", wSFCBatchBOMItem.MinFQTY);
            wParamMap.put("CreateID", wSFCBatchBOMItem.CreateID);
            wParamMap.put("CreateTime", wSFCBatchBOMItem.CreateTime);
            wParamMap.put("EditID", wLoginUser.ID);
            wParamMap.put("EditTime", wSFCBatchBOMItem.EditTime);
            wParamMap.put("Active", wSFCBatchBOMItem.Active);

            KeyHolder keyHolder = new GeneratedKeyHolder();
            SqlParameterSource wSqlParameterSource = new MapSqlParameterSource(wParamMap);

            shrisJdbcTemplate.update(wSQL, wSqlParameterSource, keyHolder);

            if (wSFCBatchBOMItem.getID() <= 0) {
                wResult = Objects.requireNonNull(keyHolder.getKey()).intValue();
                wSFCBatchBOMItem.setID(wResult);
            } else {
                wResult = wSFCBatchBOMItem.getID();
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 删除集合
     */
    public ServiceResult<Integer> DeleteList(BMSEmployee wLoginUser, List<SFCBatchBOMItem> wList,
                                             OutResult<Integer> wErrorCode) {
        ServiceResult<Integer> wResult = new ServiceResult<>(0);
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wList == null || wList.size() <= 0)
                return wResult;

            List<String> wIDList = new ArrayList<>();
            for (SFCBatchBOMItem wItem : wList) {
                wIDList.add(String.valueOf(wItem.ID));
            }
            String wSql = MessageFormat.format("delete from {1}.sfc_batchbomitem WHERE ID IN({0}) ;",
                    String.join(",", wIDList), wInstance.Result);
            this.ExecuteSqlTransaction(wSql);
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 查单条
     */
    public SFCBatchBOMItem SelectByID(BMSEmployee wLoginUser, int wID, OutResult<Integer> wErrorCode) {
        SFCBatchBOMItem wResult = new SFCBatchBOMItem();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            List<SFCBatchBOMItem> wList = SelectList(wLoginUser, wID, -1, -1, -1, -1, -1, -1, -1, -1, "", null, null,
                    wErrorCode);
            if (wList == null || wList.size() != 1)
                return wResult;
            wResult = wList.get(0);
        } catch (Exception e) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(e.toString());
        }
        return wResult;
    }

    /**
     * 条件查询集合
     */
    public List<SFCBatchBOMItem> SelectList(BMSEmployee wLoginUser, int wID, int wProductID, int wLineID, int wPartID
            , int wPartPointID, int wReplaceType, int wOutSourceType, int wCreateID, int wActive, String wMaterial,
                                            Calendar wStartTime,
                                            Calendar wEndTime, OutResult<Integer> wErrorCode) {
        List<SFCBatchBOMItem> wResultList = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResultList;
            }

            Calendar wBaseTime = Calendar.getInstance();
            wBaseTime.set(2000, 0, 1, 0, 0, 0);
            if (wStartTime == null) {
                wStartTime = wBaseTime;
            }
            if (wEndTime == null) {
                wEndTime = wBaseTime;
            }
            if (wStartTime.compareTo(wEndTime) > 0) {
                return wResultList;
            }

            String wSQL = MessageFormat.format(
                    "SELECT t.*,t1.ProductNo,t2.Name LineName,t3.Name PartName," +
                            "t3.Code PartCode,t4.Name PartPointName,t4.Code PartPointCode," +
                            "t5.Name UnitText,t6.Name Creator,t7.Name Editor FROM {0}.sfc_batchbomitem t"
                            + " left join {0}.fpc_product t1 on t.ProductID=t1.ID "
                            + " left join {0}.fmc_line t2 on t.LineID=t2.ID "
                            + " left join {0}.fpc_part t3 on t.PartID=t3.ID "
                            + " left join {0}.fpc_partpoint t4 on t.PartPointID=t4.ID "
                            + " left join {0}.cfg_unit t5 on t.UnitID=t5.ID "
                            + " left join {0}.mbs_user t6 on t.CreateID=t6.ID "
                            + " left join {0}.mbs_user t7 on t.EditID=t7.ID "
                            + "WHERE  1=1  and ( :wID <= 0 or :wID = t.ID ) and ( :wProductID " +
                            "<= 0 or :wProductID = t.ProductID ) and ( :wLineID <= 0 or :wLineID = t.LineID ) and ( " +
                            ":wPartID <= 0 or :wPartID = t.PartID ) and ( :wPartPointID <= 0 or :wPartPointID = " +
                            "t.PartPointID ) and ( :wReplaceType <= 0 or :wReplaceType = t.ReplaceType ) and ( " +
                            ":wOutSourceType <= 0 or :wOutSourceType = t.OutSourceType ) " +
                            "and (:wMaterial='''' or :wMaterial=null or (MaterialNo like ''%{1}%'' or MaterialName " +
                            "like ''%{1}%'')) " +
                            "and ( :wCreateID <= 0 or " +
                            ":wCreateID = t.CreateID ) and ( :wStartTime <=str_to_date(''2010-01-01'', ''%Y-%m-%d'') " +
                            " or " +
                            ":wStartTime <=  t.EditTime ) and ( :wEndTime <=str_to_date(''2010-01-01'', ''%Y-%m-%d'')" +
                            " " +
                            " or :wEndTime >=  t.CreateTime ) and ( :wActive < 0 or " +
                            ":wActive = t.Active );",
                    wInstance.Result, wMaterial);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("wID", wID);
            wParamMap.put("wProductID", wProductID);
            wParamMap.put("wLineID", wLineID);
            wParamMap.put("wPartID", wPartID);
            wParamMap.put("wPartPointID", wPartPointID);
            wParamMap.put("wReplaceType", wReplaceType);
            wParamMap.put("wOutSourceType", wOutSourceType);
            wParamMap.put("wCreateID", wCreateID);
            wParamMap.put("wActive", wActive);
            wParamMap.put("wStartTime", wStartTime);
            wParamMap.put("wEndTime", wEndTime);
            wParamMap.put("wMaterial", wMaterial);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                SFCBatchBOMItem wItem = new SFCBatchBOMItem();

                wItem.ID = StringUtils.parseInt(wReader.get("ID"));
                wItem.ProductID = StringUtils.parseInt(wReader.get("ProductID"));
                wItem.ProductNo = StringUtils.parseString(wReader.get("ProductNo"));
                wItem.LineID = StringUtils.parseInt(wReader.get("LineID"));
                wItem.LineName = StringUtils.parseString(wReader.get("LineName"));
                wItem.PartID = StringUtils.parseInt(wReader.get("PartID"));
                wItem.PartName = StringUtils.parseString(wReader.get("PartName"));
                wItem.PartCode = StringUtils.parseString(wReader.get("PartCode"));
                wItem.PartPointID = StringUtils.parseInt(wReader.get("PartPointID"));
                wItem.PartPointName = StringUtils.parseString(wReader.get("PartPointName"));
                wItem.PartPointCode = StringUtils.parseString(wReader.get("PartPointCode"));
                wItem.MaterialID = StringUtils.parseInt(wReader.get("MaterialID"));
                wItem.MaterialNo = StringUtils.parseString(wReader.get("MaterialNo"));
                wItem.MaterialName = StringUtils.parseString(wReader.get("MaterialName"));
                wItem.UnitID = StringUtils.parseInt(wReader.get("UnitID"));
                wItem.UnitText = StringUtils.parseString(wReader.get("UnitText"));
                wItem.FQTY = StringUtils.parseDouble(wReader.get("FQTY"));
                wItem.ReplaceType = StringUtils.parseInt(wReader.get("ReplaceType"));
                wItem.ReplaceTypeText = APSReplaceType.getEnumType(wItem.ReplaceType).getLable();
                wItem.OutSourceType = StringUtils.parseInt(wReader.get("OutSourceType"));
                wItem.OutSourceTypeText = APSOutSourceType.getEnumType(wItem.OutSourceType).getLable();
                wItem.EvenExchangeRate = StringUtils.parseDouble(wReader.get("EvenExchangeRate"));
                wItem.MinFQTY = StringUtils.parseDouble(wReader.get("MinFQTY"));
                wItem.CreateID = StringUtils.parseInt(wReader.get("CreateID"));
                wItem.Creator = StringUtils.parseString(wReader.get("Creator"));
                wItem.CreateTime = StringUtils.parseCalendar(wReader.get("CreateTime"));
                wItem.EditID = StringUtils.parseInt(wReader.get("EditID"));
                wItem.Editor = StringUtils.parseString(wReader.get("Editor"));
                wItem.EditTime = StringUtils.parseCalendar(wReader.get("EditTime"));
                wItem.Active = StringUtils.parseInt(wReader.get("Active"));

                wResultList.add(wItem);
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResultList;
    }

    public List<Integer> QueryMaterialID(BMSEmployee wLoginUser, String wMaterial, OutResult<Integer> wErrorCode) {
        List<Integer> wResult = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT ID FROM {0}.mss_material WHERE MaterialName LIKE ''%{1}%'' OR " +
                            "MaterialNo LIKE ''%{1}%'';",
                    wInstance.Result, wMaterial);

            Map<String, Object> wParamMap = new HashMap<>();

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                int wID = StringUtils.parseInt(wReader.get("ID"));
                wResult.add(wID);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 查询线库存
     */
    public Map<Integer, Double> SFC_QueryLineStockMap(BMSEmployee wLoginUser, List<Integer> wMaterialIDList,
                                                      OutResult<Integer> wErrorCode) {
        Map<Integer, Double> wResult = new HashMap<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wMaterialIDList == null || wMaterialIDList.size() <= 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format(
                    "SELECT MaterialID,FQTY FROM {0}.wms_lineedgelibrary WHERE MaterialID IN ({1}) AND FQTY>0;",
                    wInstance.Result, StringUtils.Join(",", wMaterialIDList));

            Map<String, Object> wParamMap = new HashMap<>();

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            List<WMSLineEdgeLibrary> wLibraryList = new ArrayList<>();
            for (Map<String, Object> wReader : wQueryResult) {
                WMSLineEdgeLibrary wItem = new WMSLineEdgeLibrary();
                wItem.MaterialID = StringUtils.parseInt(wReader.get("MaterialID"));
                wItem.FQTY = StringUtils.parseDouble(wReader.get("FQTY"));
                wLibraryList.add(wItem);
            }

            for (WMSLineEdgeLibrary wWMSLineEdgeLibrary : wLibraryList) {
                if (wResult.containsKey(wWMSLineEdgeLibrary.MaterialID)) {
                    continue;
                }

                List<WMSLineEdgeLibrary> wItemList =
                        wLibraryList.stream().filter(p -> p.MaterialID == wWMSLineEdgeLibrary.MaterialID).collect(Collectors.toList());
                double wFQTY = 0.0;
                for (WMSLineEdgeLibrary wItem : wItemList) {
                    wFQTY += wItem.FQTY;
                }
                wResult.put(wWMSLineEdgeLibrary.MaterialID, wFQTY);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    public MyExcelSheet GetMyExcelSheet(String wTaskIDs) {
        MyExcelSheet wResult = new MyExcelSheet();
        try {
            wResult.TitleName = "易损易耗物料领用清单";
            wResult.SheetName = wResult.TitleName;
            wResult.HeaderList = new ArrayList<>(Arrays.asList("单号", "物料编码", "物料名称", "工位", "工序", "BOM数量", "单位",
                    "必换偶换", "必修偶修", "偶换率(%)", "最小发货量", "领料需求数", "实际需求数"));
            OutResult<Integer> wErrorCode = new OutResult<>(0);
            wResult.DataList = GetDataList(BaseDAO.SysAdmin, wTaskIDs, wErrorCode);
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    private List<List<String>> GetDataList(BMSEmployee wLoginUser, String wTaskIDs, OutResult<Integer> wErrorCode) {
        List<List<String>> wResult = new ArrayList<>();
        try {
            if (StringUtils.isEmpty(wTaskIDs))
                return wResult;

            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT t2.Code 单号,t3.MaterialNo 物料编码,t3.MaterialName,t4.Name 工位,t5.Name" +
                            " 工序,t1.FQTY BOM数量,t6.Name 单位,t1.EvenExchangeRate 偶换率,t1.MinFQTY 最小发货量,t1.MaterialNumber " +
                            "领料需求数,t1.RealDemandNumber 实际需求数  " +
                            "FROM {0}.sfc_batchbomtaskbpmitem t1,{0}.sfc_batchbomtaskbpm t2,{0}" +
                            ".mss_material t3,{0}.fpc_part t4,{0}.fpc_partpoint t5,{0}.cfg_unit t6 " +
                            " " +
                            "WHERE t1.sfcbatchbomtaskid=t2.ID AND t3.ID=t1.materialID AND t1.PartID=t4.ID AND t1" +
                            ".PartPointID=t5.ID AND t6.ID=t1.UnitID and t2.ID in ({1});",
                    wInstance.Result, wTaskIDs);

            Map<String, Object> wParamMap = new HashMap<>();

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                List<String> wList = new ArrayList<>();

                wList.add(StringUtils.parseString(wReader.get("单号")));
                wList.add(StringUtils.parseString(wReader.get("物料编码")));
                wList.add(StringUtils.parseString(wReader.get("MaterialName")));
                wList.add(StringUtils.parseString(wReader.get("工位")));
                wList.add(StringUtils.parseString(wReader.get("工序")));
                wList.add(StringUtils.parseString(wReader.get("BOM数量")));
                wList.add(StringUtils.parseString(wReader.get("单位")));
                wList.add("偶换");
                wList.add("");
                wList.add(StringUtils.parseString(wReader.get("偶换率")));
                wList.add(StringUtils.parseString(wReader.get("最小发货量")));
                wList.add(StringUtils.parseString(wReader.get("领料需求数")));
                wList.add(StringUtils.parseString(wReader.get("实际需求数")));

                wResult.add(wList);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }
}
